﻿using System;
using System.Linq;
using System.IO;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Metadata.Edm;
using System.Xml;
using System.Xml.Linq;
using System.Data.EntityClient;
using System.Data.Objects.DataClasses;


namespace ma.DbSchemaProvider
{
  public class DbSchemaProvider: IDisposable
  {
    //----------------
    public DbSchemaProvider(string dataProvider, string connectionString)
    {
      _connectionString = connectionString;
      _dataProvider = dataProvider;
      // product token
      DbProviderFactory factory = DbProviderFactories.GetFactory(_dataProvider);
      var conn = factory.CreateConnection();
      conn.ConnectionString = connectionString;
      IServiceProvider se = factory as IServiceProvider;
      if(se == null) throw new Exception("Provider " + dataProvider+" is not ADO.NET provider for Entity Framework");
      var aaa = se.GetService(typeof(DbProviderServices));
      if(aaa == null) throw new Exception("Provider " + dataProvider + " is not ADO.NET provider for Entity Framework");
      var dbp = se.GetService(typeof(DbProviderServices)) as DbProviderServices;
      _providerManifestToken = dbp.GetProviderManifestToken(conn);
      DbProviderManifest manifest = dbp.GetProviderManifest(_providerManifestToken);

      _storeTypes = manifest.GetStoreTypes(); // Conversion Table - Store.Type=>CLR.Type
      //== DB Provider Model ==========================
      string tempDirectory = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());// The temporary directory for a saving schema.
      Directory.CreateDirectory(tempDirectory);
      try {
        string ssdlFile = tempDirectory + "SchemaInformationModel.ssdl";
        string mslFile = tempDirectory + "SchemaInformationModel.msl";
        XmlReader ssdlXReader = manifest.GetInformation(DbProviderManifest.StoreSchemaDefinition);
        XmlReader mslXReader = manifest.GetInformation(DbProviderManifest.StoreSchemaMapping);
        try {
          XDocument.Load(ssdlXReader).Save(ssdlFile);
          XDocument.Load(mslXReader).Save(mslFile);
        } finally {
          ssdlXReader.Close();
          mslXReader.Close();
        }
        //-- Metadata Workspace
        var mw = new System.Data.Metadata.Edm.MetadataWorkspace(new string[]{
          "res://System.Data.Entity/System.Data.Resources.DbProviderServices.ConceptualSchemaDefinition.csdl",
          ssdlFile,mslFile
        },
          new System.Reflection.Assembly[] { 
            typeof(System.Data.EntityClient.EntityProviderFactory).Assembly 
          });
        // Connection
        _entityConnection = new EntityConnection(mw, conn);
        // Entity Model
        _schemaModel = new Store.SchemaInformation(_entityConnection);
        _schemaModel.ContextOptions.LazyLoadingEnabled = false;


      } finally {
        Directory.Delete(tempDirectory, true);
      }
    }
    // fields
    protected string _connectionString;
    protected string _dataProvider;
    protected string _providerManifestToken;
    private System.Collections.ObjectModel.ReadOnlyCollection<PrimitiveType> _storeTypes; //conversion table
    private EntityConnection _entityConnection;
    private Store.SchemaInformation _schemaModel;
    // properties
    public virtual string ConnectionString { get { return _connectionString; } }
    public virtual string DataProvider { get { return _dataProvider; } }
    public virtual string ProviderManifestToken { get { return _providerManifestToken; } }

    public TableDef GetTable(string tableName)
    {
      var res = new TableDef();
      var tab = _schemaModel.Tables.Where(p => p.Name.ToUpper() == tableName).ToArray().FirstOrDefault();
      if(tab != null) {
        res.Name = tab.Name;
        res.SchemaName = tab.SchemaName;
      } else {
        var view = _schemaModel.Views.Where(p => p.Name.ToUpper() == tableName).ToArray().First();
        res.Name = view.Name;
        res.SchemaName = view.SchemaName;
        res.IsView = true;
      }
      return res;
    }

    public List<ColumnDef> GetColumns(string tableName)
    {
      var res = new List<ColumnDef>();

      EntityCollection<Store.Column> columns;
      var tab = _schemaModel.Tables.Include("Columns").Where(p => p.Name.ToUpper() == tableName).ToArray().FirstOrDefault();
      if(tab != null) {
        columns = tab.Columns;
      } else {
        var view = _schemaModel.Views.Where(p => p.Name == tableName).ToArray();
        view[0].Columns.Load();
        columns = view[0].Columns;
      }
      foreach(var item in columns) {
        string clrTypeName = null;
        var storeType = _storeTypes.FirstOrDefault(p => p.Name == item.ColumnType.TypeName);
        if(storeType == null) {
          clrTypeName = item.ColumnType.TypeName;
        } else {
          clrTypeName = storeType.ClrEquivalentType.Name;
          if(clrTypeName == "Byte[]") {
            clrTypeName = "Binary";
          }
        }

        var columnDef = new ColumnDef() {
          Name = item.Name,
          PropertyTypeName = clrTypeName,
          SQLTypeName = item.ColumnType.TypeName,
          IsIdentity = item.IsIdentity,
          IsNullable = item.IsNullable,
          IsExpression = item.IsStoreGenerated
        };
        string tn = columnDef.PropertyTypeName.ToLower();
        if(tn == "decimal") {
          columnDef.NumeicPrecision = item.ColumnType.Precision;
          columnDef.NumericScale = item.ColumnType.Scale;
        }
        if(tn == "string"
          || tn == "char" // || tn == "binary"
        ) {
          if(item.ColumnType.MaxLength < 2000) columnDef.ColumnSize = item.ColumnType.MaxLength;
        }
        res.Add(columnDef);
      }
      return res;
    }

    public List<string> GetPrimaryKeys(string tableName)
    {
      var res = new List<string>();
      var tab = _schemaModel.Tables.Include("Constraints").Where(p => p.Name.ToUpper() == tableName).ToArray().FirstOrDefault();
      if(tab != null) {
        foreach(var item in tab.Constraints) {
          var pkc = item as Store.PrimaryKeyConstraint;
          if(pkc == null) continue;
          pkc.Columns.Load();
          foreach(var itemCol in pkc.Columns) {
            res.Add(itemCol.Name);
          }

        }
      }
      return res;
    }

    public void Dispose()
    {
      _schemaModel.Dispose();
    }
  }
  //---
  public class TableDef
  {
    public string Name { get; set; }
    public string SchemaName { get; set; }
    public bool IsView { get; set; }
       
  }
  //---
  public class ColumnDef
  {
    public string Name { get; set; }
    public string PropertyTypeName { get; set; }
    public string SQLTypeName { get; set; }
    public bool Unicode { get; set; }
    public bool IsNullable { get; set; }
    public bool IsExpression { get; set; }
    public bool IsIdentity { get; set; }
    public bool IsReadOnly { get; set; }
    public bool IsFixedLenght { get; set; }

    public int? ColumnSize { get; set; }
    public int? NumeicPrecision { get; set; }
    public int? NumericScale { get; set; }
  }

}
